/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao.mysql;

import dao.ProductDAO;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import model.Product;

/**
 *
 * @author Student
 */
public class ProductDAOImpl implements ProductDAO {

    private Connection conn;

    public ProductDAOImpl(Connection conn) {
        this.conn = conn;
    }

    @Override
    public int save(Product product) throws Exception {
        String sql = "INSERT INTO product(name,description,price,category_id) values(?,?,?,?)";
        int out = 0;
        PreparedStatement ps = null;
        try {
            ps = this.conn.prepareStatement(sql);
            ps.setString(1, product.getName());
            ps.setString(2, product.getDescription());
            ps.setDouble(3, product.getPrice());
            ps.setInt(4, product.getCategory().getId());
            out = ps.executeUpdate();
            if (ps != null) {
                ps.close();
            }
        } catch (Exception ex) {
            throw ex;
        }
        return out;
    }

    @Override
    public int delete(int id) throws Exception {
        String sql = "DELETE FROM product WHERE id=?";
        int out = 0;
        PreparedStatement ps = null;
        try {
            ps = this.conn.prepareStatement(sql);
            ps.setInt(1, id);
            out = ps.executeUpdate();
        } catch (Exception ex) {
            throw ex;
        }
        return out;
    }

    @Override
    public int update(Product product) throws Exception {
        String sql = "UPDATE product set name=?,description=?,price=?,category_id=? where id=?";
        int out = 0;
        PreparedStatement ps = null;
        try{
            ps = this.conn.prepareStatement(sql);
            ps.setString(1,product.getName());
            ps.setString(2,product.getDescription());
            ps.setDouble(3, product.getPrice());
            ps.setInt(4,product.getCategory().getId());
            ps.setInt(5,product.getId());
            out = ps.executeUpdate();
        }catch(Exception ex){
            throw ex;
        }
        return out;
    }

    @Override
    public Product getProductById(int id) throws Exception {
        String sql = "select id,name,description,price,category_id from product where id=?";
        Product product = null;
        PreparedStatement ps = null;
        try {
            this.conn.setAutoCommit(false);
            ps = this.conn.prepareStatement(sql);
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                product = new Product();
                product.setId(rs.getInt(1));
                product.setName(rs.getString(2));
                product.setDescription(rs.getString(3));
                product.setPrice(rs.getDouble(4));
                product.setCategory(new CategoryDAOImpl(this.conn).getCategoryById(rs.getInt(5)));
            }
            this.conn.commit();
        } catch (Exception ex) {
            this.conn.rollback();
            throw ex;
        } finally {
            this.conn.setAutoCommit(true);
        }
        return product;
    }

    @Override
    public List<Product> getAllProduct() throws Exception {
        String sql = "Select id,name,description,price,category_id from product";
        List<Product> products = new ArrayList<Product>();
        PreparedStatement ps = null;
        try {
            ps = this.conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Product product = new Product();
                product.setId(rs.getInt(1));
                product.setName(rs.getString(2));
                product.setDescription(rs.getString(3));
                product.setPrice(rs.getDouble(4));
                product.setCategory(new CategoryDAOImpl(this.conn).getCategoryById(rs.getInt(5)));
                products.add(product);
            }
        } catch (Exception ex) {
            throw ex;
        }
        return products;
    }

    @Override
    public List<Product> getProductByCategory(int categoryId) throws Exception {
        String sql = "Select id,name,description,price,category_id from product where category_id=?";
        List<Product> products = new ArrayList<Product>();
        PreparedStatement ps = null;
        try {
            ps = this.conn.prepareStatement(sql);
            ps.setInt(1, categoryId);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Product product = new Product();
                product.setId(rs.getInt(1));
                product.setName(rs.getString(2));
                product.setDescription(rs.getString(3));
                product.setPrice(rs.getDouble(4));
                product.setCategory(new CategoryDAOImpl(this.conn).getCategoryById(rs.getInt(5)));
                products.add(product);
            }
        } catch (Exception ex) {
            throw ex;
        }
        return products;
    }
}
